﻿//Table:AS_UserInfo

#region Entity

using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using Common;
using Entity;
using Platform.Configuration;
using Platform.Data;

namespace Entity
{
    [Serializable]
    public class AS_UserInfo
    {
        private bool? m_b_sex;
        private DateTime? m_DT_borthdate;
        private DateTime? m_DT_createddate;
        private DateTime? m_DT_expiredate;
        private int? m_i_department;
        private int m_i_is_active;
        private int m_i_userid;
        private string m_str_account;
        private string m_str_address;
        private string m_str_cellphone;
        private string m_str_email;
        private string m_str_idcard;
        private string m_str_phone;
        private string m_str_postnum;
        private string m_str_pws;
        private string m_str_remark;
        private string m_str_username;

        public AS_UserInfo()
        {
            IS_ACTIVE = 1;
        }

        public int UserID
        {
            get { return m_i_userid; }
            set { m_i_userid = value; }
        }

        public string UserName
        {
            get { return m_str_username; }
            set { m_str_username = value; }
        }

        public string Account
        {
            get { return m_str_account; }
            set { m_str_account = value; }
        }

        public string PWS
        {
            get { return m_str_pws; }
            set { m_str_pws = value; }
        }

        public string Remark
        {
            get { return m_str_remark; }
            set { m_str_remark = value; }
        }

        public int IS_ACTIVE
        {
            get { return m_i_is_active; }
            set { m_i_is_active = value; }
        }

        public DateTime? ExpireDate
        {
            get { return m_DT_expiredate; }
            set { m_DT_expiredate = value; }
        }

        public DateTime? CreatedDate
        {
            get { return m_DT_createddate; }
            set { m_DT_createddate = value; }
        }

        public string Phone
        {
            get { return m_str_phone; }
            set { m_str_phone = value; }
        }

        public string CellPhone
        {
            get { return m_str_cellphone; }
            set { m_str_cellphone = value; }
        }

        public string EMail
        {
            get { return m_str_email; }
            set { m_str_email = value; }
        }

        public string IdCard
        {
            get { return m_str_idcard; }
            set { m_str_idcard = value; }
        }

        public DateTime? BorthDate
        {
            get { return m_DT_borthdate; }
            set { m_DT_borthdate = value; }
        }

        public bool? Sex
        {
            get { return m_b_sex; }
            set { m_b_sex = value; }
        }

        public string Address
        {
            get { return m_str_address; }
            set { m_str_address = value; }
        }

        public string PostNum
        {
            get { return m_str_postnum; }
            set { m_str_postnum = value; }
        }

        public int? Department
        {
            get { return m_i_department; }
            set { m_i_department = value; }
        }
    }
}

#endregion

#region EntityProcess

namespace BLL
{
    public abstract class AS_UserInfoProcess
    {
        #region Field

        protected string conn;

        #endregion

        #region Construction

        public AS_UserInfoProcess()
        {
            var connectionEncodeAndDecode = new ConnectionEncodeAndDecode();
            conn = connectionEncodeAndDecode.DecodeBase64(ConfigHelper.PlatformConnString);
        }

        #endregion

        #region public

        public bool InsertOrUpdate(AS_UserInfo AS_UserInfo, DbTransaction Transaction)
        {
            if (AS_UserInfo.UserID == 0)
                InsertAS_UserInfo(AS_UserInfo, Transaction);
            else
                UpdateAS_UserInfo(AS_UserInfo, Transaction);
            return AS_UserInfo.UserID == 0 ? false : true;
        }

        public bool DeleteAS_UserInfo(int UserID, DbTransaction Transaction)
        {
            var spName = "_DeleteAS_UserInfo";
            var storedParams = SqlHelperParameterCache.GetSpParameterSet(conn, spName);
            storedParams[0].Value = UserID;
            var result = 0;
            if (Transaction == null)
                result = SqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, spName, storedParams);
            else
                result = SqlHelper.ExecuteNonQuery((SqlTransaction) Transaction, CommandType.StoredProcedure, spName,
                    storedParams);
            return result == 0 ? false : true;
        }

        public bool DeleteAS_UserInfosDynamic(string WhereClause, DbTransaction Transaction)
        {
            var spName = "_DeleteAS_UserInfosDynamic";
            var storedParams = SqlHelperParameterCache.GetSpParameterSet(conn, spName);
            storedParams[0].Value = WhereClause;
            var result = 0;
            if (Transaction == null)
                result = SqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, spName, storedParams);
            else
                result = SqlHelper.ExecuteNonQuery((SqlTransaction) Transaction, CommandType.StoredProcedure, spName,
                    storedParams);
            return result == 0 ? false : true;
        }

        public DataTable LeftJoin<EntityName>(string On, string WhereClause, string OrderByExpression,
            DbTransaction Transaction)
        {
            DataSet ds = null;
            if (Transaction == null)
                ds = SqlHelper.ExecuteDataset(conn, CommandType.Text,
                    "select * from AS_UserInfo left join " + typeof(EntityName).Name + " on " + On +
                    (string.IsNullOrEmpty(WhereClause.Trim()) ? "" : " where " + WhereClause) + " " +
                    (string.IsNullOrEmpty(OrderByExpression.Trim()) ? "" : " order by " + OrderByExpression));
            else
                ds = SqlHelper.ExecuteDataset((SqlTransaction) Transaction, CommandType.Text,
                    "select * from AS_UserInfo left join " + typeof(EntityName).Name + " on " + On +
                    (string.IsNullOrEmpty(WhereClause.Trim()) ? "" : " where " + WhereClause) + " " +
                    (string.IsNullOrEmpty(OrderByExpression.Trim()) ? "" : " order by " + OrderByExpression));
            if (ds != null && ds.Tables.Count > 0)
            {
                AddRowNumber(ds.Tables[0]);
                return ds.Tables[0];
            }
            return null;
        }

        public AS_UserInfo SelectAS_UserInfo(int UserID, DbTransaction Transaction)
        {
            var spName = "_SelectAS_UserInfo";
            var storedParams = SqlHelperParameterCache.GetSpParameterSet(conn, spName);
            storedParams[0].Value = UserID;
            SqlDataReader rd;
            if (Transaction == null)
                rd = SqlHelper.ExecuteReader(conn, CommandType.StoredProcedure, spName, storedParams);
            else
                rd = SqlHelper.ExecuteReader((SqlTransaction) Transaction, CommandType.StoredProcedure, spName,
                    storedParams);
            if (rd.Read())
            {
                var AS_UserInfo = PopulateAS_UserInfo(rd);
                rd.Close();
                return AS_UserInfo;
            }
            return null;
        }

        public DataTable SelectAS_UserInfosAll(DbTransaction Transaction)
        {
            var spName = "_SelectAS_UserInfosAll";
            DataSet ds = null;
            if (Transaction == null)
                ds = SqlHelper.ExecuteDataset(conn, CommandType.StoredProcedure, spName);
            else
                ds = SqlHelper.ExecuteDataset((SqlTransaction) Transaction, CommandType.StoredProcedure, spName);
            if (ds != null && ds.Tables.Count > 0)
            {
                AddRowNumber(ds.Tables[0]);
                return ds.Tables[0];
            }
            return null;
        }

        public DataTable SelectAS_UserInfosDynamic(string WhereClause, string OrderByExpression,
            DbTransaction Transaction)
        {
            var spName = "_SelectAS_UserInfosDynamic";
            var storedParams = SqlHelperParameterCache.GetSpParameterSet(conn, spName);
            storedParams[0].Value = string.IsNullOrEmpty(WhereClause.Trim()) ? "1=1" : WhereClause;
            storedParams[1].Value = OrderByExpression;
            DataSet ds = null;
            if (Transaction == null)
                ds = SqlHelper.ExecuteDataset(conn, CommandType.StoredProcedure, spName, storedParams);
            else
                ds = SqlHelper.ExecuteDataset((SqlTransaction) Transaction, CommandType.StoredProcedure, spName,
                    storedParams);
            if (ds != null && ds.Tables.Count > 0)
            {
                AddRowNumber(ds.Tables[0]);
                return ds.Tables[0];
            }
            return null;
        }

        public DataTable SelectAS_UserInfosBySerialNumberID(int SerialNumberID, string OrderByExpression,
            DbTransaction Transaction)
        {
            return SelectAS_UserInfosDynamic("SerialnumberID=" + SerialNumberID, OrderByExpression, Transaction);
        }

        #endregion

        #region Private

        private int InsertAS_UserInfo(AS_UserInfo AS_UserInfo, DbTransaction Transaction)
        {
            var spName = "_InsertAS_UserInfo";
            var storedParams = SqlHelperParameterCache.GetSpParameterSet(conn, spName);
            storedParams[0].Value = AS_UserInfo.UserName;
            storedParams[1].Value = AS_UserInfo.Account;
            storedParams[2].Value = AS_UserInfo.PWS;
            storedParams[3].Value = AS_UserInfo.Remark;
            storedParams[4].Value = AS_UserInfo.IS_ACTIVE;
            storedParams[5].Value = AS_UserInfo.ExpireDate;
            storedParams[6].Value = AS_UserInfo.CreatedDate;
            storedParams[7].Value = AS_UserInfo.Phone;
            storedParams[8].Value = AS_UserInfo.CellPhone;
            storedParams[9].Value = AS_UserInfo.EMail;
            storedParams[10].Value = AS_UserInfo.IdCard;
            storedParams[11].Value = AS_UserInfo.BorthDate;
            storedParams[12].Value = AS_UserInfo.Sex;
            storedParams[13].Value = AS_UserInfo.Address;
            storedParams[14].Value = AS_UserInfo.PostNum;
            storedParams[15].Value = AS_UserInfo.Department;
            var result = 0;
            if (Transaction == null)
                result = SqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, spName, storedParams);
            else
                result = SqlHelper.ExecuteNonQuery((SqlTransaction) Transaction, CommandType.StoredProcedure, spName,
                    storedParams);
            AS_UserInfo.UserID = (int) storedParams[16].Value;
            return result;
        }

        private bool UpdateAS_UserInfo(AS_UserInfo AS_UserInfo, DbTransaction Transaction)
        {
            var spName = "_UpdateAS_UserInfo";
            var storedParams = SqlHelperParameterCache.GetSpParameterSet(conn, spName);
            storedParams[0].Value = AS_UserInfo.UserID;
            storedParams[1].Value = AS_UserInfo.UserName;
            storedParams[2].Value = AS_UserInfo.Account;
            storedParams[3].Value = AS_UserInfo.PWS;
            storedParams[4].Value = AS_UserInfo.Remark;
            storedParams[5].Value = AS_UserInfo.IS_ACTIVE;
            storedParams[6].Value = AS_UserInfo.ExpireDate;
            storedParams[7].Value = AS_UserInfo.CreatedDate;
            storedParams[8].Value = AS_UserInfo.Phone;
            storedParams[9].Value = AS_UserInfo.CellPhone;
            storedParams[10].Value = AS_UserInfo.EMail;
            storedParams[11].Value = AS_UserInfo.IdCard;
            storedParams[12].Value = AS_UserInfo.BorthDate;
            storedParams[13].Value = AS_UserInfo.Sex;
            storedParams[14].Value = AS_UserInfo.Address;
            storedParams[15].Value = AS_UserInfo.PostNum;
            storedParams[16].Value = AS_UserInfo.Department;
            var result = 0;
            if (Transaction == null)
                result = SqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, spName, storedParams);
            else
                result = SqlHelper.ExecuteNonQuery((SqlTransaction) Transaction, CommandType.StoredProcedure, spName,
                    storedParams);
            return result == 0 ? false : true;
        }

        private AS_UserInfo PopulateAS_UserInfo(IDataReader dr)
        {
            var AS_UserInfo = new AS_UserInfo();
            AS_UserInfo.UserID = dr["UserID"] == DBNull.Value ? 0 : (int) dr["UserID"];
            AS_UserInfo.UserName = dr["UserName"] == DBNull.Value ? "" : (string) dr["UserName"];
            AS_UserInfo.Account = dr["Account"] == DBNull.Value ? "" : (string) dr["Account"];
            AS_UserInfo.PWS = dr["PWS"] == DBNull.Value ? null : (string) dr["PWS"];
            AS_UserInfo.Remark = dr["Remark"] == DBNull.Value ? null : (string) dr["Remark"];
            AS_UserInfo.IS_ACTIVE = dr["IS_ACTIVE"] == DBNull.Value ? 0 : (int) dr["IS_ACTIVE"];
            AS_UserInfo.ExpireDate = dr["ExpireDate"] == DBNull.Value ? null : (DateTime?) dr["ExpireDate"];
            AS_UserInfo.CreatedDate = dr["CreatedDate"] == DBNull.Value ? null : (DateTime?) dr["CreatedDate"];
            AS_UserInfo.Phone = dr["Phone"] == DBNull.Value ? null : (string) dr["Phone"];
            AS_UserInfo.CellPhone = dr["CellPhone"] == DBNull.Value ? null : (string) dr["CellPhone"];
            AS_UserInfo.EMail = dr["EMail"] == DBNull.Value ? null : (string) dr["EMail"];
            AS_UserInfo.IdCard = dr["IdCard"] == DBNull.Value ? null : (string) dr["IdCard"];
            AS_UserInfo.BorthDate = dr["BorthDate"] == DBNull.Value ? null : (DateTime?) dr["BorthDate"];
            AS_UserInfo.Sex = dr["Sex"] == DBNull.Value ? null : (bool?) dr["Sex"];
            AS_UserInfo.Address = dr["Address"] == DBNull.Value ? null : (string) dr["Address"];
            AS_UserInfo.PostNum = dr["PostNum"] == DBNull.Value ? null : (string) dr["PostNum"];
            AS_UserInfo.Department = dr["Department"] == DBNull.Value ? null : (int?) dr["Department"];
            return AS_UserInfo;
        }

        private void AddRowNumber(DataTable dt)
        {
            dt.Columns.Add(new DataColumn("RowNumber", typeof(int)));
            for (var i = 0; i < dt.Rows.Count; i++)
                dt.Rows[i][dt.Columns.Count - 1] = i + 1;
        }

        #endregion
    }
}

#endregion